﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using VisaCRM.Common.DataLayer;
using System.Text;

namespace VisaCRM.Common.Helper
{
    public static class DBHelper
    {
        private static DataTable dataTable;
        private static SqlDataAdapter sqlDataAdapter;

        private static void CreateDataTable()
        {
            dataTable = new DataTable();
        }

        private static void CreateSqlDataAdapter()
        {
            sqlDataAdapter = new SqlDataAdapter();
        }

        public static void ExecuteSqlQuery(string query)
        {
            SqlConnection sqlConnection = DataAccess.OpenConnection();
            SqlCommand command = sqlConnection.CreateCommand();
            ExecuteSqlQuery(query, command);
        }

        public static string ExecuteSqlScalar(string query, SqlCommand command)
        {
            string output = string.Empty;
            try
            {
                output = ((int)command.ExecuteScalar()).ToString();
            }
            catch (Exception ex)
            {
               // throw new SqlExecutionException(query, ex);
            }
            return output;
        }

        public static void ExecuteSqlQuery(string query, SqlCommand command)
        {
            command.CommandText = query;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //throw new SqlExecutionException(query, ex);
            }

        }

        public static DataTable GetDataTable(string MainQuery)
        {
            try
            {
                SqlConnection sqlConnection = DataAccess.OpenConnection();
                CreateDataTable();
                CreateSqlDataAdapter();
                var command = sqlConnection.CreateCommand();
                command.CommandText = MainQuery;
                sqlDataAdapter.SelectCommand = command;
                sqlDataAdapter.Fill(dataTable);
                
            }
            catch (Exception ex)
            {
                //throw new SqlExecutionException(MainQuery, ex);
            }
            finally
            {
                DataAccess.CloseConnection();
            }
            return dataTable;
        }
    }
}
